package com.lbd.statistics.service.impl;

import com.lbd.base.service.BaseServiceImpl;
import com.lbd.project.bean.PersonalPaybackPlan;
import com.lbd.project.service.PersonalPaybackPlanService;
import com.lbd.statistics.controller.vo.InvestStatisticsVo;
import com.lbd.statistics.service.InvestStatisticsService;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
@Transactional
public class InvestStatisticsServiceImpl implements InvestStatisticsService {
    private final Logger log = Logger.getLogger(this.getClass());

    @Autowired
    public EntityManager em;


    @Override
    public InvestStatisticsVo findByTime(InvestStatisticsVo investStatictisVo) {

        Map<String, Object> parameter = new HashMap<String, Object>();
        InvestStatisticsVo vo = new InvestStatisticsVo();
        HashMap<String, Object> map = new HashMap<String, Object>();
        StringBuffer sqlStr = new StringBuffer();
        if (investStatictisVo != null) {
            if (investStatictisVo.getStartDate() != null && investStatictisVo.getStartDate().toString().length() > 0) {
                sqlStr.append(" and o.created_date >= :startDate");
                parameter.put("startDate", investStatictisVo.getStartDate());

            }
            if (investStatictisVo.getEndDate() != null && investStatictisVo.getEndDate().toString().length() > 0) {
                sqlStr.append(" and o.created_date <= :endDate");
                parameter.put("endDate", investStatictisVo.getEndDate());
            }
        }
        StringBuffer sql = new StringBuffer();
        /**
         * 查询累计投资金额、投资总笔数、总投资人数、人均累计投资金额
         */
        try {
            sql.append("select NULLIF(sum(buy_Amt),0)as invest_amount,COUNT(o.id) as invest_num,COUNT(DISTINCT o.user_id) as investor_num from `Order` o where 1=1");
//			sql.append("select sum(r.actual_amount) as invest_amount,COUNT(r.id) as invest_num,COUNT(DISTINCT r.user_id) as investor_num  from trading_record r");
//			sql.append(" LEFT JOIN `order` o ON r.order_id=o.id ");
//			sql.append(" LEFT JOIN target_info t ON o.target_id=t.id ");
            sql.append(" and o.state in(6,7)");
            sql.append(sqlStr);
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            // 累计投资金额（￥）
            BigDecimal investAmount = BigDecimal.ZERO;
            if (map != null && map.get("invest_amount") != null) {
                investAmount = BigDecimal.valueOf((double) map.get("invest_amount"));
            }
            vo.setInvestAmount(investAmount);
            // 投资总笔数（笔）
            vo.setInvestNum(map == null || map.get("invest_num") == null ? 0 : Integer.valueOf(map.get("invest_num").toString()));
            // 总投资人数（人）
            Integer investorNum = 0;
            if (map != null && map.get("investor_num") != null) {
                investorNum = Integer.valueOf(map.get("investor_num").toString());
            }
            vo.setInvestorNum(investorNum);
            // 人均累计投资金额（￥）
            BigDecimal perInvestmentAmount = BigDecimal.ZERO;
            if (investorNum > 0) {
                perInvestmentAmount = investAmount.divide(BigDecimal.valueOf(investorNum.doubleValue())).setScale(2, BigDecimal.ROUND_HALF_EVEN);
            }
            vo.setPerInvestmentAmount(perInvestmentAmount);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询累计投资金额、投资总笔数、总投资人数、人均累计投资金额失败,e=" + e.getMessage());
        }

        //累计冻结金额
        try {
            sql = new StringBuffer();
            sql.append("select NULLIF(sum(buy_Amt),0) as frozen_amount from `Order` o where 1=1");
            sql.append(" and o.state =3");
            sql.append(sqlStr);
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());
            // 累计冻结金额（￥）
            BigDecimal frozenAmount = BigDecimal.ZERO;
            if (map != null && map.get("frozen_amount") != null) {
                frozenAmount = BigDecimal.valueOf((double) map.get("frozen_amount"));
            }
            /**
             * 累计总冻结金额（￥）
             */
            vo.setFrozenAmount(frozenAmount);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询累计冻结金额失败,e=" + e.getMessage());
        }

        //累计投资收益（￥）
        try {
            sql = new StringBuffer();
            sql.append("select NULLIF(sum(lx),0) as investments from personal_payback_plan o where 1=1");
            sql.append(" and o.state in(1,3) ");
            parameter = new HashMap<String, Object>();
            if (investStatictisVo != null) {
                if (investStatictisVo.getStartDate() != null && investStatictisVo.getStartDate().toString().length() > 0) {
                    sql.append(" and o.hkrq >= :startDate");
                    parameter.put("startDate", investStatictisVo.getStartDate());

                }
                if (investStatictisVo.getEndDate() != null && investStatictisVo.getEndDate().toString().length() > 0) {
                    sql.append(" and o.hkrq <= :endDate");
                    parameter.put("endDate", investStatictisVo.getEndDate());
                }
            }
            map = (HashMap<String, Object>) findMapByPageNativeQuery(parameter, sql.toString());

            // 累计投资收益（￥）
            BigDecimal investments = BigDecimal.ZERO;
            if (map != null && map.get("investments") != null) {
                investments = (BigDecimal) map.get("investments");
            }
            /**
             * 累计投资收益（￥）
             */
            vo.setInvestments(investments);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("查询累计冻结金额失败,e=" + e.getMessage());
        }

        vo.setStartDate(investStatictisVo.getStartDate());
        vo.setEndDate(investStatictisVo.getEndDate());

        System.out.println(vo.toString());
        return vo;
    }

    /**
     * nativesql返回Map
     *
     * @param parameter nativeSql 的参数
     * @param nativeSql nativeSql 字符串
     * @return
     */
    public Map findMapByPageNativeQuery(Map<String, Object> parameter, String nativeSql) {
        Query nativeQuery = em.createNativeQuery(nativeSql);
        nativeQuery.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        for (String key : parameter.keySet()) {
            nativeQuery.setParameter(key, parameter.get(key));
        }
        return (Map) nativeQuery.getSingleResult();
    }
}
